package edu.upc.dao.consulta.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Component;

import edu.upc.dao.consulta.ConsultaOfertaLaboralDao;
import edu.upc.model.OfertaLaboral;

@Component
public class ConsultaOfertaLaboralDaoImpl implements ConsultaOfertaLaboralDao {
	
	@Autowired
	DataSource dataSource;
	
	public List<OfertaLaboral> obtenerOfertaLaboral(String codigo) {
		
		String sql = "select id_avi , cargo_avi, descripcion_avi,jornadaLaboral_avi, disponibilidad_avi from tb_aviso"; // where cargo_avi=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<OfertaLaboral> lst = new ArrayList<OfertaLaboral>();
		try {
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			//ps.setString(1, codigo);   //Los filtros
			
			rs = ps.executeQuery();
/*			
			while(rs.next()){
				OfertaLaboral ofertaLaboral = new OfertaLaboral();
				ofertaLaboral.setCargo_avi(rs.getString("cargo_avi"));
				lst.add(ofertaLaboral);	
			}
		*/	
			advanceToNextRow(rs);
			lst.add(mapOfertaLaboral(rs));
		} catch (SQLException e) {
			throw new RuntimeException("SQL exception occurred finding by merchant number", e);
		} finally {
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}
		return lst;
	}
	
	/**
	 * Maps a row returned from a query of T_RESTAURANT to a Restaurant object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	
	private OfertaLaboral mapOfertaLaboral(ResultSet rs) throws SQLException {
		OfertaLaboral ofertaLaboral=new OfertaLaboral();
		ofertaLaboral.setId_avi(Integer.parseInt(rs.getString("id_avi")));
		ofertaLaboral.setCargo_avi(rs.getString("cargo_avi"));
		ofertaLaboral.setDescripcion_avi(rs.getString("descripcion_avi"));
		ofertaLaboral.setJornadaLaboral_avi(rs.getString("jornadaLaboral_avi"));
		ofertaLaboral.setDisponibilidad_avi(rs.getString("disponibilidad_avi"));

		return ofertaLaboral;
	}
	
	/**
	 * Advances a ResultSet to the next row and throws an exception if there are no rows.
	 * @param rs the ResultSet to advance
	 * @throws EmptyResultDataAccessException if there is no next row
	 * @throws SQLException
	 */
	private void advanceToNextRow(ResultSet rs) throws EmptyResultDataAccessException, SQLException {
		if (!rs.next()) {
			throw new EmptyResultDataAccessException(1);
		}
	}
}	


